Carregamento e Preparação dos Dados

Carregamento dos Dados

InteliStatus <- read_csv("../../../../../../datasources-project/InteliStatus.csv")
## Rows: 7245975 Columns: 3
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (3): ID, STATUS, STATUS_DATA
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
InteliVeiculo <- read_csv("../../../../../../datasources-project/InteliVeiculo.csv")
## Rows: 139707 Columns: 6
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (6): ID, MODELL, FARBAU, FARBIN, ZIEL_LAND, PR
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
InteliResultados <- read_csv("../../../../../../datasources-project/InteliResultados.csv")
## Rows: 1932313 Columns: 5
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (5): ID, RESULT_ID, RESULT_DESCRIPTION, CAPTURE_TIME, STATUS
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
InteliFalhas <- read_csv("../../../../../../datasources-project/InteliFalhas.csv")
## Rows: 679073 Columns: 12
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (7): ID, DATA DETECCAO, PONTO, LOC, POS, TYPE_TEXT, COLUNA
## dbl (5): LOC_ID, POS_ID, TYPE_ID, VIEW_ID, LINHA
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Inteli5Merges <- read.csv2("../../../../../../datasources-project/5-ids.csv", stringsAsFactors = FALSE)

Visualização das primeiras Linhas

head(InteliStatus)
## # A tibble: 6 × 3
##   ID           STATUS STATUS_DATA               
##   <chr>        <chr>  <chr>                     
## 1 2024-1422099 R210   2024-04-02-00.00.37.000000
## 2 2024-1422099 R212   2024-04-02-00.00.37.000000
## 3 2024-1431276 R213   2024-04-02-01.38.41.000000
## 4 2024-1431276 R215   2024-04-02-01.38.41.000000
## 5 2024-1452139 R300   2024-04-02-01.39.09.000000
## 6 2024-1422007 R213   2024-04-02-01.40.39.000000
head(InteliVeiculo)
## # A tibble: 6 × 6
##   ID           MODELL FARBAU FARBIN ZIEL_LAND PR                                
##   <chr>        <chr>  <chr>  <chr>  <chr>     <chr>                             
## 1 2024-1322010 R111Q4 B4B4   XK     VY        LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D T…
## 2 2024-1332013 R111Q4 B4B4   XK     VY        LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D T…
## 3 2024-1332022 R111Q4 B4B4   XK     VY        LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D T…
## 4 2024-1332321 R111Q4 B4B4   XK     VY        LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D T…
## 5 2024-1332322 R111Q4 B4B4   XK     VY        LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D T…
## 6 2024-1332323 R111Q4 B4B4   XK     VY        LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D T…
head(InteliResultados)
## # A tibble: 6 × 5
##   ID           RESULT_ID RESULT_DESCRIPTION CAPTURE_TIME               STATUS
##   <chr>        <chr>     <chr>              <chr>                      <chr> 
## 1 2024-3821101 53P007    ABASTEC. CÂMBIO    2019-02-14-08.20.57.000000 NOK   
## 2 2024-3851264 53P007    ABASTEC. CÂMBIO    2019-02-14-08.31.27.000000 NOK   
## 3 2024-3851264 53P007    ABASTEC. CÂMBIO    2019-02-14-08.31.57.000000 NOK   
## 4 2024-3851264 53P007    ABASTEC. CÂMBIO    2019-02-14-08.33.57.000000 NOK   
## 5 2024-3851264 53P007    ABASTEC. CÂMBIO    2019-02-14-08.34.27.000000 NOK   
## 6 2024-3851264 53P007    ABASTEC. CÂMBIO    2019-02-14-08.34.57.000000 NOK
head(InteliFalhas)
## # A tibble: 6 × 12
##   ID           `DATA DETECCAO` PONTO LOC_ID LOC   POS_ID POS   TYPE_ID TYPE_TEXT
##   <chr>        <chr>           <chr>  <dbl> <chr>  <dbl> <chr>   <dbl> <chr>    
## 1 2024-3712154 2010-01-29-02.… RODA… 5.56e3 Tamp…     -2 <NA>       70 "070 - R…
## 2 2024-3722068 2010-01-29-02.… RODA… 5.56e3 Tamp…     -2 <NA>       70 "070 - R…
## 3 2024-3732080 2010-01-29-02.… RODA… 7.06e3 Reve…      5 dire…      70 "070 - R…
## 4 2024-3732079 2010-01-29-02.… RODA… 5.85e3 Port…      5 dire…      70 "070 - R…
## 5 2024-3711076 2010-01-29-02.… RODA… 4.66e3 Alav…     -2 <NA>        5 "005 - B…
## 6 2024-3732094 2010-01-29-04.… RODA… 9.88e7 Regu…      3 esqu…      31 "031 - P…
## # ℹ 3 more variables: VIEW_ID <dbl>, COLUNA <chr>, LINHA <dbl>
head(Inteli5Merges)
##   INTELI_FALHAS_DATA.DETECCAO INTELI_FALHAS_PONTO INTELI_FALHAS_LOC_ID
## 1  2024-10-30-00.20.10.000000                 ZP7                 5522
## 2  2024-10-30-00.20.10.000000                 ZP7                 5522
## 3  2024-10-30-00.20.10.000000                 ZP7                 5522
## 4  2024-10-30-00.20.10.000000                 ZP7                 5522
## 5  2024-10-30-00.20.10.000000                 ZP7                 5522
## 6  2024-10-30-00.20.10.000000                 ZP7                 5522
##   INTELI_FALHAS_LOC INTELI_FALHAS_POS_ID INTELI_FALHAS_POS
## 1   Tampa dianteira                    1           externo
## 2   Tampa dianteira                    1           externo
## 3   Tampa dianteira                    1           externo
## 4   Tampa dianteira                    1           externo
## 5   Tampa dianteira                    1           externo
## 6   Tampa dianteira                    1           externo
##   INTELI_FALHAS_TYPE_ID INTELI_FALHAS_TYPE_TEXT INTELI_FALHAS_VIEW_ID
## 1                   124            124 - Cavaco                 661.0
## 2                   124            124 - Cavaco                 661.0
## 3                   124            124 - Cavaco                 661.0
## 4                   124            124 - Cavaco                 661.0
## 5                   124            124 - Cavaco                 661.0
## 6                   124            124 - Cavaco                 661.0
##   INTELI_FALHAS_COLUNA INTELI_FALHAS_LINHA INTELI_RESULTADOS_RESULT_ID
## 1                    G                 2.0                    53TM2106
## 2                    G                 2.0                    53TM2106
## 3                    G                 2.0                    53TM2106
## 4                    G                 2.0                    53TM2106
## 5                    G                 2.0                    53TM2106
## 6                    G                 2.0                    53TM2106
##   INTELI_RESULTADOS_RESULT_DESCRIPTION INTELI_RESULTADOS_CAPTURE_TIME
## 1 210601 - Coxim do motor na longarina     2024-10-29-07.44.58.000000
## 2 210601 - Coxim do motor na longarina     2024-10-29-07.44.58.000000
## 3 210601 - Coxim do motor na longarina     2024-10-29-07.44.58.000000
## 4 210601 - Coxim do motor na longarina     2024-10-29-07.44.58.000000
## 5 210601 - Coxim do motor na longarina     2024-10-29-07.44.58.000000
## 6 210601 - Coxim do motor na longarina     2024-10-29-07.44.58.000000
##   INTELI_RESULTADOS_STATUS INTELI_VEICULO_MODELL INTELI_VEICULO_FARBAU
## 1                      NOK              BZ32K3                    B4B4
## 2                      NOK              BZ32K3                    B4B4
## 3                      NOK              BZ32K3                    B4B4
## 4                      NOK              BZ32K3                    B4B4
## 5                      NOK              BZ32K3                    B4B4
## 6                      NOK              BZ32K3                    B4B4
##   INTELI_VEICULO_FARBIN INTELI_VEICULO_ZIEL_LAND
## 1                    WK                      VY 
## 2                    WK                      VY 
## 3                    WK                      VY 
## 4                    WK                      VY 
## 5                    WK                      VY 
## 6                    WK                      VY 
##                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  INTELI_VEICULO_PR
## 1 LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP1 DFV:G02 GSP:G1A HAT:IA0 REI:I08 BAT:J1H KSU:KA0 KUH:KK1 KZB:KL0 BLE:KN0 KAR:K8G LEA:L0L GKV:L04 BGK:NY0 RUF:NZ0 SIB:N2E WIV:QG0 SPR:QH0 SEA:QI3 CHR:QJ1 KAS:QK0 TVE:QV0 VOS:Q1A SNR:S37 GMO:T26 ABA:UD0 AFH:UG1 ESS:U9C FHW:VF0 FGS:VL1 REL:V0A STV:0AS TUE:0A2 RST:0B1 FAD:0FR EDF:0ID ASY:0K0 SZU:0NW HIA:0N1 ZSS:0RZ SAU:0SH ZFM:0TA BTA:0VJ GKH:0YF KLZ:0Y7 UGH:00A BRS:1AS KRS:1A4 FRO:1C1 AHV:1D0 TYP:1EB AKB:1E7 RER:1G1 DFH:1JB BAH:1KA RAA:1NL LEN:1N1 ABR:1PA TWU:1SA BOW:1S1 AED:1TW VBK:1T2 KBX:1W0 ATA:1X0 BAV:1ZS KRM:1Z0 UEA:2A0 LSS:2C5 CWV:2D0 LRA:2FE CHA:2H0 STF:2JZ SWP:2UA SHI:2U4 FRI:2V1 ZKV:3B4 SHM:3C7 MIK:3D2 DEI:3FA LBH:3GA LER:3H0 KOV:3J4 SIE:3L1 BOB:3MJ HIS:3NB HAL:3P7 KOH:3Q6 GPR:3U1 GDM:3W5 SHA:3ZB TSV:4AP WSS:4GF TKV:4I3 SSH:4KC IRS:4L2 PBH:4P0 RSV:4QU FEH:4R4 AIB:4UF GNF:4U0 SAB:4X1 HAK:4Z4 GRV:5A8 KSA:5C0 TRF:5D1 LAC:5K0 EIH:5MA HGD:5N0 ASR:5RQ ASL:5SJ SON:5XH FEU:6A0 MAS:6E3 BBO:6FF DFO:6F2 KUG:6KA AWV:6K0 FLA:6LA GPS:6M0 HIM:6NA HBV:6PH SAG:6Q2 ASE:6YA AGM:7GX FVS:7G0 MFA:7J1 RDK:7K1 SNA:7L6 EIL:7M5 LOR:7P0 CDR:7QA NAV:7Q0 SLE:7R6 ESI:7W0 EPH:7X3 SPU:7Y0 GEN:8GG LIA:8G0 HSW:8IT FLS:8K1 HEW:8M1 SWS:8N4 LWR:8Q1 LSE:8RJ SBR:8SA LEL:8S2 NEL:8TA GRA:8T2 RAO:8UK NES:8WA WWA:8W0 SWR:8X0 SNH:8Y0 ANT:8ZG MKU:8Z4 HKA:9AB GRB:9E0 RAU:9JA INB:9MF SWZ:9PE SGK:9P9 EDW:9V6 AUD:9WG VTV:9ZX 
## 2 LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP1 DFV:G02 GSP:G1A HAT:IA0 REI:I08 BAT:J1H KSU:KA0 KUH:KK1 KZB:KL0 BLE:KN0 KAR:K8G LEA:L0L GKV:L04 BGK:NY0 RUF:NZ0 SIB:N2E WIV:QG0 SPR:QH0 SEA:QI3 CHR:QJ1 KAS:QK0 TVE:QV0 VOS:Q1A SNR:S37 GMO:T26 ABA:UD0 AFH:UG1 ESS:U9C FHW:VF0 FGS:VL1 REL:V0A STV:0AS TUE:0A2 RST:0B1 FAD:0FR EDF:0ID ASY:0K0 SZU:0NW HIA:0N1 ZSS:0RZ SAU:0SH ZFM:0TA BTA:0VJ GKH:0YF KLZ:0Y7 UGH:00A BRS:1AS KRS:1A4 FRO:1C1 AHV:1D0 TYP:1EB AKB:1E7 RER:1G1 DFH:1JB BAH:1KA RAA:1NL LEN:1N1 ABR:1PA TWU:1SA BOW:1S1 AED:1TW VBK:1T2 KBX:1W0 ATA:1X0 BAV:1ZS KRM:1Z0 UEA:2A0 LSS:2C5 CWV:2D0 LRA:2FE CHA:2H0 STF:2JZ SWP:2UA SHI:2U4 FRI:2V1 ZKV:3B4 SHM:3C7 MIK:3D2 DEI:3FA LBH:3GA LER:3H0 KOV:3J4 SIE:3L1 BOB:3MJ HIS:3NB HAL:3P7 KOH:3Q6 GPR:3U1 GDM:3W5 SHA:3ZB TSV:4AP WSS:4GF TKV:4I3 SSH:4KC IRS:4L2 PBH:4P0 RSV:4QU FEH:4R4 AIB:4UF GNF:4U0 SAB:4X1 HAK:4Z4 GRV:5A8 KSA:5C0 TRF:5D1 LAC:5K0 EIH:5MA HGD:5N0 ASR:5RQ ASL:5SJ SON:5XH FEU:6A0 MAS:6E3 BBO:6FF DFO:6F2 KUG:6KA AWV:6K0 FLA:6LA GPS:6M0 HIM:6NA HBV:6PH SAG:6Q2 ASE:6YA AGM:7GX FVS:7G0 MFA:7J1 RDK:7K1 SNA:7L6 EIL:7M5 LOR:7P0 CDR:7QA NAV:7Q0 SLE:7R6 ESI:7W0 EPH:7X3 SPU:7Y0 GEN:8GG LIA:8G0 HSW:8IT FLS:8K1 HEW:8M1 SWS:8N4 LWR:8Q1 LSE:8RJ SBR:8SA LEL:8S2 NEL:8TA GRA:8T2 RAO:8UK NES:8WA WWA:8W0 SWR:8X0 SNH:8Y0 ANT:8ZG MKU:8Z4 HKA:9AB GRB:9E0 RAU:9JA INB:9MF SWZ:9PE SGK:9P9 EDW:9V6 AUD:9WG VTV:9ZX 
## 3 LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP1 DFV:G02 GSP:G1A HAT:IA0 REI:I08 BAT:J1H KSU:KA0 KUH:KK1 KZB:KL0 BLE:KN0 KAR:K8G LEA:L0L GKV:L04 BGK:NY0 RUF:NZ0 SIB:N2E WIV:QG0 SPR:QH0 SEA:QI3 CHR:QJ1 KAS:QK0 TVE:QV0 VOS:Q1A SNR:S37 GMO:T26 ABA:UD0 AFH:UG1 ESS:U9C FHW:VF0 FGS:VL1 REL:V0A STV:0AS TUE:0A2 RST:0B1 FAD:0FR EDF:0ID ASY:0K0 SZU:0NW HIA:0N1 ZSS:0RZ SAU:0SH ZFM:0TA BTA:0VJ GKH:0YF KLZ:0Y7 UGH:00A BRS:1AS KRS:1A4 FRO:1C1 AHV:1D0 TYP:1EB AKB:1E7 RER:1G1 DFH:1JB BAH:1KA RAA:1NL LEN:1N1 ABR:1PA TWU:1SA BOW:1S1 AED:1TW VBK:1T2 KBX:1W0 ATA:1X0 BAV:1ZS KRM:1Z0 UEA:2A0 LSS:2C5 CWV:2D0 LRA:2FE CHA:2H0 STF:2JZ SWP:2UA SHI:2U4 FRI:2V1 ZKV:3B4 SHM:3C7 MIK:3D2 DEI:3FA LBH:3GA LER:3H0 KOV:3J4 SIE:3L1 BOB:3MJ HIS:3NB HAL:3P7 KOH:3Q6 GPR:3U1 GDM:3W5 SHA:3ZB TSV:4AP WSS:4GF TKV:4I3 SSH:4KC IRS:4L2 PBH:4P0 RSV:4QU FEH:4R4 AIB:4UF GNF:4U0 SAB:4X1 HAK:4Z4 GRV:5A8 KSA:5C0 TRF:5D1 LAC:5K0 EIH:5MA HGD:5N0 ASR:5RQ ASL:5SJ SON:5XH FEU:6A0 MAS:6E3 BBO:6FF DFO:6F2 KUG:6KA AWV:6K0 FLA:6LA GPS:6M0 HIM:6NA HBV:6PH SAG:6Q2 ASE:6YA AGM:7GX FVS:7G0 MFA:7J1 RDK:7K1 SNA:7L6 EIL:7M5 LOR:7P0 CDR:7QA NAV:7Q0 SLE:7R6 ESI:7W0 EPH:7X3 SPU:7Y0 GEN:8GG LIA:8G0 HSW:8IT FLS:8K1 HEW:8M1 SWS:8N4 LWR:8Q1 LSE:8RJ SBR:8SA LEL:8S2 NEL:8TA GRA:8T2 RAO:8UK NES:8WA WWA:8W0 SWR:8X0 SNH:8Y0 ANT:8ZG MKU:8Z4 HKA:9AB GRB:9E0 RAU:9JA INB:9MF SWZ:9PE SGK:9P9 EDW:9V6 AUD:9WG VTV:9ZX 
## 4 LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP1 DFV:G02 GSP:G1A HAT:IA0 REI:I08 BAT:J1H KSU:KA0 KUH:KK1 KZB:KL0 BLE:KN0 KAR:K8G LEA:L0L GKV:L04 BGK:NY0 RUF:NZ0 SIB:N2E WIV:QG0 SPR:QH0 SEA:QI3 CHR:QJ1 KAS:QK0 TVE:QV0 VOS:Q1A SNR:S37 GMO:T26 ABA:UD0 AFH:UG1 ESS:U9C FHW:VF0 FGS:VL1 REL:V0A STV:0AS TUE:0A2 RST:0B1 FAD:0FR EDF:0ID ASY:0K0 SZU:0NW HIA:0N1 ZSS:0RZ SAU:0SH ZFM:0TA BTA:0VJ GKH:0YF KLZ:0Y7 UGH:00A BRS:1AS KRS:1A4 FRO:1C1 AHV:1D0 TYP:1EB AKB:1E7 RER:1G1 DFH:1JB BAH:1KA RAA:1NL LEN:1N1 ABR:1PA TWU:1SA BOW:1S1 AED:1TW VBK:1T2 KBX:1W0 ATA:1X0 BAV:1ZS KRM:1Z0 UEA:2A0 LSS:2C5 CWV:2D0 LRA:2FE CHA:2H0 STF:2JZ SWP:2UA SHI:2U4 FRI:2V1 ZKV:3B4 SHM:3C7 MIK:3D2 DEI:3FA LBH:3GA LER:3H0 KOV:3J4 SIE:3L1 BOB:3MJ HIS:3NB HAL:3P7 KOH:3Q6 GPR:3U1 GDM:3W5 SHA:3ZB TSV:4AP WSS:4GF TKV:4I3 SSH:4KC IRS:4L2 PBH:4P0 RSV:4QU FEH:4R4 AIB:4UF GNF:4U0 SAB:4X1 HAK:4Z4 GRV:5A8 KSA:5C0 TRF:5D1 LAC:5K0 EIH:5MA HGD:5N0 ASR:5RQ ASL:5SJ SON:5XH FEU:6A0 MAS:6E3 BBO:6FF DFO:6F2 KUG:6KA AWV:6K0 FLA:6LA GPS:6M0 HIM:6NA HBV:6PH SAG:6Q2 ASE:6YA AGM:7GX FVS:7G0 MFA:7J1 RDK:7K1 SNA:7L6 EIL:7M5 LOR:7P0 CDR:7QA NAV:7Q0 SLE:7R6 ESI:7W0 EPH:7X3 SPU:7Y0 GEN:8GG LIA:8G0 HSW:8IT FLS:8K1 HEW:8M1 SWS:8N4 LWR:8Q1 LSE:8RJ SBR:8SA LEL:8S2 NEL:8TA GRA:8T2 RAO:8UK NES:8WA WWA:8W0 SWR:8X0 SNH:8Y0 ANT:8ZG MKU:8Z4 HKA:9AB GRB:9E0 RAU:9JA INB:9MF SWZ:9PE SGK:9P9 EDW:9V6 AUD:9WG VTV:9ZX 
## 5 LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP1 DFV:G02 GSP:G1A HAT:IA0 REI:I08 BAT:J1H KSU:KA0 KUH:KK1 KZB:KL0 BLE:KN0 KAR:K8G LEA:L0L GKV:L04 BGK:NY0 RUF:NZ0 SIB:N2E WIV:QG0 SPR:QH0 SEA:QI3 CHR:QJ1 KAS:QK0 TVE:QV0 VOS:Q1A SNR:S37 GMO:T26 ABA:UD0 AFH:UG1 ESS:U9C FHW:VF0 FGS:VL1 REL:V0A STV:0AS TUE:0A2 RST:0B1 FAD:0FR EDF:0ID ASY:0K0 SZU:0NW HIA:0N1 ZSS:0RZ SAU:0SH ZFM:0TA BTA:0VJ GKH:0YF KLZ:0Y7 UGH:00A BRS:1AS KRS:1A4 FRO:1C1 AHV:1D0 TYP:1EB AKB:1E7 RER:1G1 DFH:1JB BAH:1KA RAA:1NL LEN:1N1 ABR:1PA TWU:1SA BOW:1S1 AED:1TW VBK:1T2 KBX:1W0 ATA:1X0 BAV:1ZS KRM:1Z0 UEA:2A0 LSS:2C5 CWV:2D0 LRA:2FE CHA:2H0 STF:2JZ SWP:2UA SHI:2U4 FRI:2V1 ZKV:3B4 SHM:3C7 MIK:3D2 DEI:3FA LBH:3GA LER:3H0 KOV:3J4 SIE:3L1 BOB:3MJ HIS:3NB HAL:3P7 KOH:3Q6 GPR:3U1 GDM:3W5 SHA:3ZB TSV:4AP WSS:4GF TKV:4I3 SSH:4KC IRS:4L2 PBH:4P0 RSV:4QU FEH:4R4 AIB:4UF GNF:4U0 SAB:4X1 HAK:4Z4 GRV:5A8 KSA:5C0 TRF:5D1 LAC:5K0 EIH:5MA HGD:5N0 ASR:5RQ ASL:5SJ SON:5XH FEU:6A0 MAS:6E3 BBO:6FF DFO:6F2 KUG:6KA AWV:6K0 FLA:6LA GPS:6M0 HIM:6NA HBV:6PH SAG:6Q2 ASE:6YA AGM:7GX FVS:7G0 MFA:7J1 RDK:7K1 SNA:7L6 EIL:7M5 LOR:7P0 CDR:7QA NAV:7Q0 SLE:7R6 ESI:7W0 EPH:7X3 SPU:7Y0 GEN:8GG LIA:8G0 HSW:8IT FLS:8K1 HEW:8M1 SWS:8N4 LWR:8Q1 LSE:8RJ SBR:8SA LEL:8S2 NEL:8TA GRA:8T2 RAO:8UK NES:8WA WWA:8W0 SWR:8X0 SNH:8Y0 ANT:8ZG MKU:8Z4 HKA:9AB GRB:9E0 RAU:9JA INB:9MF SWZ:9PE SGK:9P9 EDW:9V6 AUD:9WG VTV:9ZX 
## 6 LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP1 DFV:G02 GSP:G1A HAT:IA0 REI:I08 BAT:J1H KSU:KA0 KUH:KK1 KZB:KL0 BLE:KN0 KAR:K8G LEA:L0L GKV:L04 BGK:NY0 RUF:NZ0 SIB:N2E WIV:QG0 SPR:QH0 SEA:QI3 CHR:QJ1 KAS:QK0 TVE:QV0 VOS:Q1A SNR:S37 GMO:T26 ABA:UD0 AFH:UG1 ESS:U9C FHW:VF0 FGS:VL1 REL:V0A STV:0AS TUE:0A2 RST:0B1 FAD:0FR EDF:0ID ASY:0K0 SZU:0NW HIA:0N1 ZSS:0RZ SAU:0SH ZFM:0TA BTA:0VJ GKH:0YF KLZ:0Y7 UGH:00A BRS:1AS KRS:1A4 FRO:1C1 AHV:1D0 TYP:1EB AKB:1E7 RER:1G1 DFH:1JB BAH:1KA RAA:1NL LEN:1N1 ABR:1PA TWU:1SA BOW:1S1 AED:1TW VBK:1T2 KBX:1W0 ATA:1X0 BAV:1ZS KRM:1Z0 UEA:2A0 LSS:2C5 CWV:2D0 LRA:2FE CHA:2H0 STF:2JZ SWP:2UA SHI:2U4 FRI:2V1 ZKV:3B4 SHM:3C7 MIK:3D2 DEI:3FA LBH:3GA LER:3H0 KOV:3J4 SIE:3L1 BOB:3MJ HIS:3NB HAL:3P7 KOH:3Q6 GPR:3U1 GDM:3W5 SHA:3ZB TSV:4AP WSS:4GF TKV:4I3 SSH:4KC IRS:4L2 PBH:4P0 RSV:4QU FEH:4R4 AIB:4UF GNF:4U0 SAB:4X1 HAK:4Z4 GRV:5A8 KSA:5C0 TRF:5D1 LAC:5K0 EIH:5MA HGD:5N0 ASR:5RQ ASL:5SJ SON:5XH FEU:6A0 MAS:6E3 BBO:6FF DFO:6F2 KUG:6KA AWV:6K0 FLA:6LA GPS:6M0 HIM:6NA HBV:6PH SAG:6Q2 ASE:6YA AGM:7GX FVS:7G0 MFA:7J1 RDK:7K1 SNA:7L6 EIL:7M5 LOR:7P0 CDR:7QA NAV:7Q0 SLE:7R6 ESI:7W0 EPH:7X3 SPU:7Y0 GEN:8GG LIA:8G0 HSW:8IT FLS:8K1 HEW:8M1 SWS:8N4 LWR:8Q1 LSE:8RJ SBR:8SA LEL:8S2 NEL:8TA GRA:8T2 RAO:8UK NES:8WA WWA:8W0 SWR:8X0 SNH:8Y0 ANT:8ZG MKU:8Z4 HKA:9AB GRB:9E0 RAU:9JA INB:9MF SWZ:9PE SGK:9P9 EDW:9V6 AUD:9WG VTV:9ZX 
##             ID INTELI_STATUS_STATUS  INTELI_STATUS_STATUS_DATA
## 1 2024-4421153                 A700 2024-10-21-06.31.58.000000
## 2 2024-4421153                 R100 2024-10-26-03.09.05.000000
## 3 2024-4421153                 R210 2024-10-26-04.02.22.000000
## 4 2024-4421153                 R212 2024-10-26-04.02.22.000000
## 5 2024-4421153                 R300 2024-10-27-17.17.46.000000
## 6 2024-4421153                 R500 2024-10-28-10.32.39.000000

Verificação da estrutura dos Dados

str(InteliStatus)
## spc_tbl_ [7,245,975 × 3] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
##  $ ID         : chr [1:7245975] "2024-1422099" "2024-1422099" "2024-1431276" "2024-1431276" ...
##  $ STATUS     : chr [1:7245975] "R210" "R212" "R213" "R215" ...
##  $ STATUS_DATA: chr [1:7245975] "2024-04-02-00.00.37.000000" "2024-04-02-00.00.37.000000" "2024-04-02-01.38.41.000000" "2024-04-02-01.38.41.000000" ...
##  - attr(*, "spec")=
##   .. cols(
##   ..   ID = col_character(),
##   ..   STATUS = col_character(),
##   ..   STATUS_DATA = col_character()
##   .. )
##  - attr(*, "problems")=<externalptr>
str(InteliVeiculo)
## spc_tbl_ [139,707 × 6] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
##  $ ID       : chr [1:139707] "2024-1322010" "2024-1332013" "2024-1332022" "2024-1332321" ...
##  $ MODELL   : chr [1:139707] "R111Q4" "R111Q4" "R111Q4" "R111Q4" ...
##  $ FARBAU   : chr [1:139707] "B4B4" "B4B4" "B4B4" "B4B4" ...
##  $ FARBIN   : chr [1:139707] "XK" "XK" "XK" "XK" ...
##  $ ZIEL_LAND: chr [1:139707] "VY" "VY" "VY" "VY" ...
##  $ PR       : chr [1:139707] "LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D TPL:B41 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP0 GSP:G0C"| __truncated__ "LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D TPL:B41 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP0 GSP:G0C"| __truncated__ "LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D TPL:B41 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP0 GSP:G0C"| __truncated__ "LRV:AV1 MSF:AW0 AUS:A8A BLB:B1D TPL:B41 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A PAM:GP0 GSP:G0C"| __truncated__ ...
##  - attr(*, "spec")=
##   .. cols(
##   ..   ID = col_character(),
##   ..   MODELL = col_character(),
##   ..   FARBAU = col_character(),
##   ..   FARBIN = col_character(),
##   ..   ZIEL_LAND = col_character(),
##   ..   PR = col_character()
##   .. )
##  - attr(*, "problems")=<externalptr>
str(InteliResultados)
## spc_tbl_ [1,932,313 × 5] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
##  $ ID                : chr [1:1932313] "2024-3821101" "2024-3851264" "2024-3851264" "2024-3851264" ...
##  $ RESULT_ID         : chr [1:1932313] "53P007" "53P007" "53P007" "53P007" ...
##  $ RESULT_DESCRIPTION: chr [1:1932313] "ABASTEC. CÂMBIO" "ABASTEC. CÂMBIO" "ABASTEC. CÂMBIO" "ABASTEC. CÂMBIO" ...
##  $ CAPTURE_TIME      : chr [1:1932313] "2019-02-14-08.20.57.000000" "2019-02-14-08.31.27.000000" "2019-02-14-08.31.57.000000" "2019-02-14-08.33.57.000000" ...
##  $ STATUS            : chr [1:1932313] "NOK" "NOK" "NOK" "NOK" ...
##  - attr(*, "spec")=
##   .. cols(
##   ..   ID = col_character(),
##   ..   RESULT_ID = col_character(),
##   ..   RESULT_DESCRIPTION = col_character(),
##   ..   CAPTURE_TIME = col_character(),
##   ..   STATUS = col_character()
##   .. )
##  - attr(*, "problems")=<externalptr>
str(InteliFalhas)
## spc_tbl_ [679,073 × 12] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
##  $ ID           : chr [1:679073] "2024-3712154" "2024-3722068" "2024-3732080" "2024-3732079" ...
##  $ DATA DETECCAO: chr [1:679073] "2010-01-29-02.25.25.000000" "2010-01-29-02.38.57.000000" "2010-01-29-02.44.54.000000" "2010-01-29-02.53.28.000000" ...
##  $ PONTO        : chr [1:679073] "RODAGEM" "RODAGEM" "RODAGEM" "RODAGEM" ...
##  $ LOC_ID       : num [1:679073] 5559 5559 7057 5851 4661 ...
##  $ LOC          : chr [1:679073] "Tampa traseira" "Tampa traseira" "Revestimento coluna A" "Porta traseira" ...
##  $ POS_ID       : num [1:679073] -2 -2 5 5 -2 3 3 5 4 3 ...
##  $ POS          : chr [1:679073] NA NA "direita" "direita" ...
##  $ TYPE_ID      : num [1:679073] 70 70 70 70 5 31 70 70 70 70 ...
##  $ TYPE_TEXT    : chr [1:679073] "070 - Ruído" "070 - Ruído" "070 - Ruído" "070 - Ruído" ...
##  $ VIEW_ID      : num [1:679073] NA NA NA NA NA NA NA NA NA NA ...
##  $ COLUNA       : chr [1:679073] NA NA NA NA ...
##  $ LINHA        : num [1:679073] NA NA NA NA NA NA NA NA NA NA ...
##  - attr(*, "spec")=
##   .. cols(
##   ..   ID = col_character(),
##   ..   `DATA DETECCAO` = col_character(),
##   ..   PONTO = col_character(),
##   ..   LOC_ID = col_double(),
##   ..   LOC = col_character(),
##   ..   POS_ID = col_double(),
##   ..   POS = col_character(),
##   ..   TYPE_ID = col_double(),
##   ..   TYPE_TEXT = col_character(),
##   ..   VIEW_ID = col_double(),
##   ..   COLUNA = col_character(),
##   ..   LINHA = col_double()
##   .. )
##  - attr(*, "problems")=<externalptr>
str(Inteli5Merges)
## 'data.frame':    368248 obs. of  23 variables:
##  $ INTELI_FALHAS_DATA.DETECCAO         : chr  "2024-10-30-00.20.10.000000" "2024-10-30-00.20.10.000000" "2024-10-30-00.20.10.000000" "2024-10-30-00.20.10.000000" ...
##  $ INTELI_FALHAS_PONTO                 : chr  "ZP7" "ZP7" "ZP7" "ZP7" ...
##  $ INTELI_FALHAS_LOC_ID                : int  5522 5522 5522 5522 5522 5522 5522 5522 5522 5522 ...
##  $ INTELI_FALHAS_LOC                   : chr  "Tampa dianteira" "Tampa dianteira" "Tampa dianteira" "Tampa dianteira" ...
##  $ INTELI_FALHAS_POS_ID                : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ INTELI_FALHAS_POS                   : chr  "externo" "externo" "externo" "externo" ...
##  $ INTELI_FALHAS_TYPE_ID               : int  124 124 124 124 124 124 124 124 124 124 ...
##  $ INTELI_FALHAS_TYPE_TEXT             : chr  "124 - Cavaco" "124 - Cavaco" "124 - Cavaco" "124 - Cavaco" ...
##  $ INTELI_FALHAS_VIEW_ID               : chr  "661.0" "661.0" "661.0" "661.0" ...
##  $ INTELI_FALHAS_COLUNA                : chr  "G" "G" "G" "G" ...
##  $ INTELI_FALHAS_LINHA                 : chr  "2.0" "2.0" "2.0" "2.0" ...
##  $ INTELI_RESULTADOS_RESULT_ID         : chr  "53TM2106" "53TM2106" "53TM2106" "53TM2106" ...
##  $ INTELI_RESULTADOS_RESULT_DESCRIPTION: chr  "210601 - Coxim do motor na longarina" "210601 - Coxim do motor na longarina" "210601 - Coxim do motor na longarina" "210601 - Coxim do motor na longarina" ...
##  $ INTELI_RESULTADOS_CAPTURE_TIME      : chr  "2024-10-29-07.44.58.000000" "2024-10-29-07.44.58.000000" "2024-10-29-07.44.58.000000" "2024-10-29-07.44.58.000000" ...
##  $ INTELI_RESULTADOS_STATUS            : chr  "NOK" "NOK" "NOK" "NOK" ...
##  $ INTELI_VEICULO_MODELL               : chr  "BZ32K3  " "BZ32K3  " "BZ32K3  " "BZ32K3  " ...
##  $ INTELI_VEICULO_FARBAU               : chr  "B4B4" "B4B4" "B4B4" "B4B4" ...
##  $ INTELI_VEICULO_FARBIN               : chr  "WK" "WK" "WK" "WK" ...
##  $ INTELI_VEICULO_ZIEL_LAND            : chr  "VY " "VY " "VY " "VY " ...
##  $ INTELI_VEICULO_PR                   : chr  "LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A"| __truncated__ "LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A"| __truncated__ "LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A"| __truncated__ "LRV:AV1 MSF:AW5 AUS:A8C BLB:B1D TPL:B41 RAD:CB1 MOT:DS8 ASG:EA0 ECO:EF0 ONL:EL0 MKE:EM0 RCO:EZ1 AAU:E0A FZS:F0A"| __truncated__ ...
##  $ ID                                  : chr  "2024-4421153" "2024-4421153" "2024-4421153" "2024-4421153" ...
##  $ INTELI_STATUS_STATUS                : chr  "A700" "R100" "R210" "R212" ...
##  $ INTELI_STATUS_STATUS_DATA           : chr  "2024-10-21-06.31.58.000000" "2024-10-26-03.09.05.000000" "2024-10-26-04.02.22.000000" "2024-10-26-04.02.22.000000" ...

Resumo Estatístico e Descrição dos Dados

Resumo estatístico

cat("\n\n\n===== Inteli Status =====\n\n\n")
## 
## 
## 
## ===== Inteli Status =====
summary(InteliStatus)
##       ID               STATUS          STATUS_DATA       
##  Length:7245975     Length:7245975     Length:7245975    
##  Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character
cat("\n\n\n===== Inteli Veiculo =====\n\n\n")
## 
## 
## 
## ===== Inteli Veiculo =====
summary(InteliVeiculo)
##       ID               MODELL             FARBAU             FARBIN         
##  Length:139707      Length:139707      Length:139707      Length:139707     
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##   ZIEL_LAND              PR           
##  Length:139707      Length:139707     
##  Class :character   Class :character  
##  Mode  :character   Mode  :character
cat("\n\n\n===== Inteli Resultados =====\n\n\n")
## 
## 
## 
## ===== Inteli Resultados =====
summary(InteliResultados)
##       ID             RESULT_ID         RESULT_DESCRIPTION CAPTURE_TIME      
##  Length:1932313     Length:1932313     Length:1932313     Length:1932313    
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##     STATUS         
##  Length:1932313    
##  Class :character  
##  Mode  :character
cat("\n\n\n===== Inteli Falhas =====\n\n\n")
## 
## 
## 
## ===== Inteli Falhas =====
summary(InteliFalhas)
##       ID            DATA DETECCAO         PONTO               LOC_ID        
##  Length:679073      Length:679073      Length:679073      Min.   :    1001  
##  Class :character   Class :character   Class :character   1st Qu.:    5145  
##  Mode  :character   Mode  :character   Mode  :character   Median :    5559  
##                                                           Mean   : 3520407  
##                                                           3rd Qu.:    5851  
##                                                           Max.   :99008995  
##                                                                             
##      LOC                POS_ID            POS               TYPE_ID        
##  Length:679073      Min.   :     -2   Length:679073      Min.   :       1  
##  Class :character   1st Qu.:      1   Class :character   1st Qu.:      39  
##  Mode  :character   Median :     10   Mode  :character   Median :      89  
##                     Mean   :  49464                      Mean   : 5766978  
##                     3rd Qu.:     17                      3rd Qu.:     126  
##                     Max.   :9799976                      Max.   :99008934  
##                                                                            
##   TYPE_TEXT            VIEW_ID         COLUNA              LINHA      
##  Length:679073      Min.   :661.0   Length:679073      Min.   :1.00   
##  Class :character   1st Qu.:673.0   Class :character   1st Qu.:2.00   
##  Mode  :character   Median :689.0   Mode  :character   Median :3.00   
##                     Mean   :699.3                      Mean   :3.25   
##                     3rd Qu.:723.0                      3rd Qu.:4.00   
##                     Max.   :778.0                      Max.   :6.00   
##                     NA's   :98563                      NA's   :98564
cat("\n\n\n===== Inteli 5 Merges =====\n\n\n")
## 
## 
## 
## ===== Inteli 5 Merges =====
summary(Inteli5Merges)
##  INTELI_FALHAS_DATA.DETECCAO INTELI_FALHAS_PONTO INTELI_FALHAS_LOC_ID
##  Length:368248               Length:368248       Min.   : 5000       
##  Class :character            Class :character    1st Qu.: 5141       
##  Mode  :character            Mode  :character    Median : 5559       
##                                                  Mean   :16379       
##                                                  3rd Qu.: 6485       
##                                                  Max.   :70925       
##  INTELI_FALHAS_LOC  INTELI_FALHAS_POS_ID INTELI_FALHAS_POS 
##  Length:368248      Min.   :     -1      Length:368248     
##  Class :character   1st Qu.:      1      Class :character  
##  Mode  :character   Median :     10      Mode  :character  
##                     Mean   : 129663                        
##                     3rd Qu.:     17                        
##                     Max.   :9799949                        
##  INTELI_FALHAS_TYPE_ID INTELI_FALHAS_TYPE_TEXT INTELI_FALHAS_VIEW_ID
##  Min.   :       4      Length:368248           Length:368248        
##  1st Qu.:      23      Class :character        Class :character     
##  Median :      87      Mode  :character        Mode  :character     
##  Mean   : 5818569                                                   
##  3rd Qu.:     126                                                   
##  Max.   :98721258                                                   
##  INTELI_FALHAS_COLUNA INTELI_FALHAS_LINHA INTELI_RESULTADOS_RESULT_ID
##  Length:368248        Length:368248       Length:368248              
##  Class :character     Class :character    Class :character           
##  Mode  :character     Mode  :character    Mode  :character           
##                                                                      
##                                                                      
##                                                                      
##  INTELI_RESULTADOS_RESULT_DESCRIPTION INTELI_RESULTADOS_CAPTURE_TIME
##  Length:368248                        Length:368248                 
##  Class :character                     Class :character              
##  Mode  :character                     Mode  :character              
##                                                                     
##                                                                     
##                                                                     
##  INTELI_RESULTADOS_STATUS INTELI_VEICULO_MODELL INTELI_VEICULO_FARBAU
##  Length:368248            Length:368248         Length:368248        
##  Class :character         Class :character      Class :character     
##  Mode  :character         Mode  :character      Mode  :character     
##                                                                      
##                                                                      
##                                                                      
##  INTELI_VEICULO_FARBIN INTELI_VEICULO_ZIEL_LAND INTELI_VEICULO_PR 
##  Length:368248         Length:368248            Length:368248     
##  Class :character      Class :character         Class :character  
##  Mode  :character      Mode  :character         Mode  :character  
##                                                                   
##                                                                   
##                                                                   
##       ID            INTELI_STATUS_STATUS INTELI_STATUS_STATUS_DATA
##  Length:368248      Length:368248        Length:368248            
##  Class :character   Class :character     Class :character         
##  Mode  :character   Mode  :character     Mode  :character         
##                                                                   
##                                                                   
## 

Ajuste do Dataset

InteliStatus <- InteliStatus %>%
  mutate(
    ID = as.character(ID),
    STATUS = as.factor(STATUS),
    STATUS_DATA = as.POSIXct(STATUS_DATA, format="%Y-%m-%d-%H.%M.%S", tz="UTC")
)

InteliVeiculo <- InteliVeiculo %>%
  mutate(
    ID = as.character(ID),
    MODELL = as.factor(MODELL),
    FARBAU = as.factor(FARBAU),
    FARBIN = as.factor(FARBIN),
    ZIEL_LAND = as.factor(ZIEL_LAND),
    PR = as.character(PR)
)

InteliResultados <- InteliResultados %>%
  mutate(
    ID = as.character(ID),
    RESULT_ID = as.factor(RESULT_ID),
    RESULT_DESCRIPTION = as.character(RESULT_DESCRIPTION),
    CAPTURE_TIME = as.POSIXct(CAPTURE_TIME, format="%Y-%m-%d-%H.%M.%S", tz="UTC"),
    STATUS = as.factor(STATUS)
)


InteliFalhas <- InteliFalhas %>%
  mutate(
    ID = as.character(ID),
    `DATA DETECCAO` = as.POSIXct(`DATA DETECCAO`, format="%Y-%m-%d-%H.%M.%S", tz="UTC"),
    PONTO=as.factor(PONTO),
    LOC_ID=as.factor(LOC_ID),
    LOC=as.factor(LOC),
    POS_ID=as.factor(POS_ID),
    POS=as.factor(POS),
    TYPE_ID=as.factor(TYPE_ID),
    TYPE_TEXT=as.factor(TYPE_TEXT),
    VIEW_ID=as.factor(VIEW_ID),
    COLUNA=as.factor(COLUNA),
    LINHA=as.factor(LINHA)
)

Inteli5Merges <- Inteli5Merges %>%
    mutate(
      ID = as.character((ID)),
      
      INTELI_STATUS_STATUS = as.factor(INTELI_STATUS_STATUS),
      INTELI_STATUS_STATUS_DATA = as.POSIXct(INTELI_STATUS_STATUS_DATA, format="%Y-%m-%d-%H.%M.%S", tz="UTC"),
    
      INTELI_VEICULO_MODELL = as.factor(INTELI_VEICULO_MODELL),
      INTELI_VEICULO_FARBAU = as.factor(INTELI_VEICULO_FARBAU),
      INTELI_VEICULO_FARBIN = as.factor(INTELI_VEICULO_FARBIN),
      INTELI_VEICULO_ZIEL_LAND = as.factor(INTELI_VEICULO_ZIEL_LAND),
      INTELI_VEICULO_PR = as.character(INTELI_VEICULO_PR),
    
      INTELI_RESULTADOS_RESULT_ID = as.factor(INTELI_RESULTADOS_RESULT_ID),
      INTELI_RESULTADOS_RESULT_DESCRIPTION = as.character(INTELI_RESULTADOS_RESULT_DESCRIPTION),
      INTELI_RESULTADOS_CAPTURE_TIME = as.POSIXct(INTELI_RESULTADOS_CAPTURE_TIME, format="%Y-%m-%d-%H.%M.%S", tz="UTC"),
      INTELI_RESULTADOS_STATUS = as.factor(INTELI_RESULTADOS_STATUS),
    
      INTELI_FALHAS_PONTO=as.factor(INTELI_FALHAS_PONTO),
      INTELI_FALHAS_LOC_ID=as.factor(INTELI_FALHAS_LOC_ID),
      INTELI_FALHAS_LOC=as.factor(INTELI_FALHAS_LOC),
      INTELI_FALHAS_POS=as.factor(INTELI_FALHAS_POS),
      INTELI_FALHAS_TYPE_ID=as.factor(INTELI_FALHAS_TYPE_ID),
      INTELI_FALHAS_TYPE_TEXT=as.factor(INTELI_FALHAS_TYPE_TEXT),
      INTELI_FALHAS_VIEW_ID=as.factor(INTELI_FALHAS_VIEW_ID),
      INTELI_FALHAS_COLUNA=as.factor(INTELI_FALHAS_COLUNA),
      INTELI_FALHAS_LINHA=as.factor(INTELI_FALHAS_LINHA)
    )

Resumo estatístico corrigido

cat("\n\n\n===== Inteli Status =====\n\n\n")
## 
## 
## 
## ===== Inteli Status =====
summary(InteliStatus)
##       ID                STATUS         STATUS_DATA                    
##  Length:7245975     M690   : 151868   Min.   :2024-04-02 00:00:37.00  
##  Class :character   L790   : 142322   1st Qu.:2024-06-22 17:31:25.00  
##  Mode  :character   G700   : 139738   Median :2024-08-24 15:54:55.00  
##                     G600   : 139731   Mean   :2024-08-25 01:48:40.78  
##                     G800   : 139725   3rd Qu.:2024-10-30 15:48:50.00  
##                     G710   : 139723   Max.   :2025-01-27 17:10:38.00  
##                     (Other):6392868
cat("\n\n\n===== Inteli Veiculo =====\n\n\n")
## 
## 
## 
## ===== Inteli Veiculo =====
summary(InteliVeiculo)
##       ID                MODELL          FARBAU      FARBIN       ZIEL_LAND     
##  Length:139707      R111Q4 :70878   B4B4   :62141   WJ:17664   VY     :110363  
##  Class :character   BZ31T4 :19890   2R2R   :31778   WK:22891   AR     : 17415  
##  Mode  :character   BZ33K3 :13927   7Z7Z   :26854   XK:99152   ME     :  5550  
##                     BZ32K3 : 8319   A1A1   :12312              KC     :  2386  
##                     BZ3SK3 : 7081   6K6K   : 2421              VR     :  1481  
##                     R11SQ4 : 4201   B4A1   : 1846              BRA    :   877  
##                     (Other):15411   (Other): 2355              (Other):  1635  
##       PR           
##  Length:139707     
##  Class :character  
##  Mode  :character  
##                    
##                    
##                    
## 
cat("\n\n\n===== Inteli Resultados =====\n\n\n")
## 
## 
## 
## ===== Inteli Resultados =====
summary(InteliResultados)
##       ID               RESULT_ID       RESULT_DESCRIPTION
##  Length:1932313     53TM3201: 149066   Length:1932313    
##  Class :character   53TM6504: 143931   Class :character  
##  Mode  :character   53TM2101: 136367   Mode  :character  
##                     53TM3202: 131237                     
##                     53TM6902: 110774                     
##                     53TM3111:  97615                     
##                     (Other) :1163323                     
##   CAPTURE_TIME                 STATUS       
##  Min.   :2019-02-14 08:20:57   NOK:1932313  
##  1st Qu.:2024-10-31 22:35:28                
##  Median :2024-11-25 08:13:39                
##  Mean   :2024-11-15 17:58:16                
##  3rd Qu.:2024-12-17 06:18:26                
##  Max.   :2025-01-28 11:20:36                
## 
cat("\n\n\n===== Inteli Falhas =====\n\n\n")
## 
## 
## 
## ===== Inteli Falhas =====
summary(InteliFalhas)
##       ID            DATA DETECCAO                        PONTO       
##  Length:679073      Min.   :2010-01-29 02:25:25.00   ZP7    :530968  
##  Class :character   1st Qu.:2024-06-18 07:45:05.00   ZP5    : 59327  
##  Mode  :character   Median :2024-08-21 18:36:37.00   ZP6    : 28758  
##                     Mean   :2024-08-21 14:01:15.04   ZP5A   : 28539  
##                     3rd Qu.:2024-10-29 03:29:09.00   RODAGEM: 10332  
##                     Max.   :2025-01-27 17:15:24.00   DKA_ZP8:  8259  
##                                                      (Other): 12890  
##      LOC_ID                        LOC             POS_ID      
##  5559   :110595   Tampa traseira     :110595   1      :189036  
##  5751   : 76536   Porta dianteira    : 76536   10     :159030  
##  5851   : 76358   Porta traseira     : 76358   17     :152265  
##  5522   : 69613   Tampa dianteira    : 69613   2      : 47415  
##  5103   : 58266   Teto               : 58266   -1     : 26634  
##  5055   : 52672   Pára-lama dianteiro: 52672   3      : 19925  
##  (Other):235033   (Other)            :235033   (Other): 84768  
##                POS            TYPE_ID      
##  externo         :189036   126    :119041  
##  direito externo :159030   4      : 83338  
##  esquerdo externo:152265   81     : 62723  
##  interno         : 47415   125    : 29034  
##  *               : 26634   73     : 27401  
##  (Other)         : 93402   40     : 23022  
##  NA's            : 11291   (Other):334514  
##                                    TYPE_TEXT           VIEW_ID      
##  126 - Sujeiras, contamina\\u00e7\\u00f5es:118154   661    : 67583  
##  004 - Amassado                           : 83338   673    : 58861  
##  081 - Caroço                             : 62723   665    : 56572  
##  125 - Amassado (Montagem)                : 29034   675    : 45160  
##  073 - Sujo                               : 27401   715    : 36287  
##  040 - Ajuste Incorreto                   : 23022   (Other):316047  
##  (Other)                                  :335401   NA's   : 98563  
##      COLUNA        LINHA       
##  F      :105788   1   : 53701  
##  G      : 91272   2   :124516  
##  C      : 89821   3   :152415  
##  B      : 84715   4   :147095  
##  E      : 80352   5   : 76596  
##  (Other):128487   6   : 26186  
##  NA's   : 98638   NA's: 98564
cat("\n\n\n===== Inteli 5 Merges =====\n\n\n")
## 
## 
## 
## ===== Inteli 5 Merges =====
summary(Inteli5Merges)
##  INTELI_FALHAS_DATA.DETECCAO INTELI_FALHAS_PONTO INTELI_FALHAS_LOC_ID
##  Length:368248               DKA_ZP8:104888      5559   : 56944      
##  Class :character            ZP5    : 66560      5103   : 56144      
##  Mode  :character            ZP5A   :  7224      51371  : 39184      
##                              ZP7    :189576      5055   : 31472      
##                                                  5751   : 25592      
##                                                  53002  : 24856      
##                                                  (Other):134056      
##                    INTELI_FALHAS_LOC  INTELI_FALHAS_POS_ID
##  Tampa traseira             : 56944   Min.   :     -1     
##  Teto                       : 56144   1st Qu.:      1     
##  Coluna A                   : 39184   Median :     10     
##  Pára-lama dianteiro        : 31472   Mean   : 129663     
##  Porta dianteira            : 25592   3rd Qu.:     17     
##  Canal de escoamento de água: 24856   Max.   :9799949     
##  (Other)                    :134056                       
##         INTELI_FALHAS_POS  INTELI_FALHAS_TYPE_ID
##  externo         :100272   4      : 54872       
##  direito externo : 82584   126    : 44568       
##  esquerdo externo: 79168   40     : 29624       
##  interno         : 44344   144    : 25024       
##  *               : 22456   73     : 23744       
##  esquerdo interno:  9968   87     : 23616       
##  (Other)         : 29456   (Other):166800       
##                               INTELI_FALHAS_TYPE_TEXT INTELI_FALHAS_VIEW_ID
##  004 - Amassado                           : 54872            : 62440       
##  126 - Sujeiras, contamina\\u00e7\\u00f5es: 44568     665.0  : 56144       
##  040 - Ajuste Incorreto                   : 29624     675.0  : 41880       
##  144 - Sujeira de KTL                     : 25024     748.0  : 39184       
##  073 - Sujo                               : 23744     689.0  : 24856       
##  087 - Ponto de solda queimado/furado     : 23616     661.0  : 23296       
##  (Other)                                  :166800     (Other):120448       
##  INTELI_FALHAS_COLUNA INTELI_FALHAS_LINHA INTELI_RESULTADOS_RESULT_ID
##  C      :68024           :62440           53TM3201: 27520            
##  F      :66624        1.0:46632           53TM2108: 26472            
##         :62440        2.0:84824           53TM6504: 25448            
##  G      :56656        3.0:79840           53TM2101: 25000            
##  B      :49720        4.0:57056           53TM3202: 24944            
##  D      :35560        5.0: 9968           53TM6902: 19848            
##  (Other):29224        6.0:27488           (Other) :219016            
##  INTELI_RESULTADOS_RESULT_DESCRIPTION INTELI_RESULTADOS_CAPTURE_TIME  
##  Length:368248                        Min.   :2024-10-29 07:44:58.00  
##  Class :character                     1st Qu.:2024-12-03 08:07:22.00  
##  Mode  :character                     Median :2025-01-13 08:23:35.00  
##                                       Mean   :2024-12-25 08:38:01.66  
##                                       3rd Qu.:2025-01-17 10:46:01.00  
##                                       Max.   :2025-01-27 15:55:50.00  
##                                                                       
##                                        INTELI_RESULTADOS_STATUS
##  NOK                                               :277272     
##  NOK                                               : 90976     
##                                                                
##                                                                
##                                                                
##                                                                
##                                                                
##  INTELI_VEICULO_MODELL INTELI_VEICULO_FARBAU INTELI_VEICULO_FARBIN
##  BZ31T4  : 85120       2R2R: 60200           WK: 64400            
##  BZ32K3  : 64400       7Z7Z:179584           XK:303848            
##  BZ38Q4  : 94464       B4B4:128464                                
##  R111Q4  :124264                                                  
##                                                                   
##                                                                   
##                                                                   
##  INTELI_VEICULO_ZIEL_LAND INTELI_VEICULO_PR       ID           
##  AR : 42560               Length:368248      Length:368248     
##  ARG: 42560               Class :character   Class :character  
##  BRA: 47232               Mode  :character   Mode  :character  
##  VY :235896                                                    
##                                                                
##                                                                
##                                                                
##  INTELI_STATUS_STATUS INTELI_STATUS_STATUS_DATA       
##  L402   : 11262       Min.   :2024-10-21 06:30:48.00  
##  L412   : 11262       1st Qu.:2024-12-02 14:35:22.00  
##  L501   : 11187       Median :2025-01-14 06:31:17.00  
##  G600   :  8425       Mean   :2024-12-23 06:36:37.51  
##  G605   :  8425       3rd Qu.:2025-01-17 10:57:59.00  
##  G630   :  8425       Max.   :2025-01-27 17:10:38.00  
##  (Other):309262

União de Datasets

Para fazer uma análise geral, de como os dados se unem entre si, foi identificada que a coluna ID presente em todos os datasets são correlacionáveis. Portanto, usando o google colab com recursos computacionais de nuvem, foram unidos os 5 ids que mais aparecem nos dados e que possuem registros em todas as tabelas, sendo o dataset Inteli5Merges

Descrição das variáveis

  • InteliStatus: Contém informações sobre os status gerados pelas etapas de montagem.

  • InteliVeiculo: Informações sobre os veículos como código geral da montadora.

  • InteliResultados: Resultados de medições a partir de instrumentos de IOT.
  • InteliFalhas: Registro de falhas identificadas durante as operações.

Inteli 5 Merges

Base criada através da união dos 5 IDs com mais registros em todas as tabelas. Feito para validar como os dados se relacionam e identificar correlações.

Análise Univariada

Visualização das distribuições

create_plots <- function(data, table_name) {
  categorical_vars <- data %>%
    select(where(is.factor)) %>%
    colnames()
  
  for (var in categorical_vars) {
    p <- ggplot(data, aes_string(x = var)) +
      geom_bar() +
      theme_minimal() +
      labs(title = paste("Distribuição de", var, "na tabela", table_name),
           x = var, y = "Contagem")
    print(p)
  }
}

Inteli Status

create_plots(InteliStatus, "InteliStatus")
## Warning: `aes_string()` was deprecated in ggplot2 3.0.0.
## ℹ Please use tidy evaluation idioms with `aes()`.
## ℹ See also `vignette("ggplot2-in-packages")` for more information.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

Inteli Veiculo

create_plots(InteliVeiculo, "InteliVeiculo")

Inteli Resultados

create_plots(InteliResultados, "InteliResultados")

Inteli Falhas

create_plots(InteliFalhas, "InteliFalhas")

Inteli 5 Merges

create_plots(Inteli5Merges, "Inteli5Merges")

Identificação de outliers

create_barplot <- function(data, table_name) {
  categorical_vars <- data %>%
    select(where(is.factor)) %>%
    colnames()
  
  for (var in categorical_vars) {
    # Contar a frequência de cada categoria
    counts <- data %>%
      count(.data[[var]], name = "frequency")
    
    p <- ggplot(counts, aes(x = reorder(.data[[var]], -frequency), y = frequency)) +
      geom_bar(stat = "identity", fill = "steelblue") +
      geom_text(aes(label = frequency), size = 3, angle = 90) +
      labs(
        title = paste("Distribuição de", var, "na tabela", table_name),
        x = var,
        y = "Frequência"
      ) +
      theme_minimal() +
      theme(axis.text.x = element_text(angle = 90, hjust = 1))  # Rotaciona labels se houver muitas categorias
    
    print(p)
  }
}

Inteli Status

create_barplot(InteliStatus, 'InteliStatus')

Inteli Veiculos

create_barplot(InteliVeiculo, "InteliVeiculo")

Inteli Resultados

create_barplot(InteliResultados, "InteliResultados")

Inteli Falhas

create_barplot(InteliFalhas, "InteliFalhas")

Inteli 5 Merges

create_barplot(Inteli5Merges, "Inteli5Merges")

Análise Bivariada

create_categorical_correlation_plot <- function(data, table_name) {
  categorical_vars <- data %>%
    select(where(is.factor))
  
  var_names <- colnames(categorical_vars)
  n <- length(var_names)
  
  correlation_matrix <- matrix(0, nrow = n, ncol = n, dimnames = list(var_names, var_names))
  
  for (i in 1:n) {
    for (j in 1:n) {
      if (i == j) {
        correlation_matrix[i, j] <- 1
      } else {
        contingency_table <- table(categorical_vars[[i]], categorical_vars[[j]])
        correlation_matrix[i, j] <- assocstats(contingency_table)$cramer
      }
    }
  }
  
  correlation_df <- as.data.frame(as.table(correlation_matrix)) %>%
    rename(Var1 = Var1, Var2 = Var2, value = Freq)

  p <- ggplot(correlation_df, aes(Var1, Var2, fill = value)) +
    geom_tile() +
    geom_text(aes(label = round(value, 2)), color = "black", size = 4) + 
    scale_fill_gradient2(low = "blue", mid = "white", high = "red", midpoint = 0.5, limits = c(0, 1)) +
    labs(
      title = paste("Correlação entre Variáveis Categóricas na Tabela", table_name),
      x = NULL,
      y = NULL,
      fill = "V de Cramer"
    ) +
    theme_minimal() +
    theme(axis.text.x = element_text(angle = 45, hjust = 1))
  
  print(p)
}

Visualização de relações entre variáveis

Inteli Status

create_categorical_correlation_plot(InteliStatus, "InteliStatus")

Inteli Veiculo

create_categorical_correlation_plot(InteliVeiculo, "InteliVeiculo")

Inteli Resultados

create_categorical_correlation_plot(InteliResultados, "InteliResultados")
## Warning: Removed 2 rows containing missing values or values outside the scale range
## (`geom_text()`).

Inteli Falhas

create_categorical_correlation_plot(InteliFalhas, "InteliFalhas")
## Warning: Removed 46 rows containing missing values or values outside the scale range
## (`geom_text()`).

Inteli 5 Merges

create_categorical_correlation_plot(Inteli5Merges, "Inteli5Merges")

Análise Multivariada

Análise de Componentes Principais (PCA)

A análise de componentes principais (PCA) não é necessária nesse projeto, visto que não há features numéricas, sendo todas apenas categóricas ou descritivas.

Conclusão e Discussão

Sumário das Descobertas

  • Nem todos os Ids aparecem em todas as tabelas
  • A união dos dados gera um dataset gigantesco.
  • Todos os status são NOK
  • A base de veículos possui muitos dados de um modelo específico, e menos de outros

Discussão sobre Limitações e Possíveis Melhorias

  • Melhorar balanceamento dos dados

  • Melhorar estratégia de união dos dados usada, sumarizando status para uma análise detalhada